package data;

import java.util.List;

import models.Permit;
import models.Teacher;

import org.sql2o.Sql2o;

public class PermitDAO implements IPermitDAO {
	private static Sql2o database;
	static {
		database = new Connection().open();
	}
	
	public Permit get(int id) {
		String sql = "select id, title, description from Permit where id = :id";
		return database.createQuery(sql).addParameter("id", id).executeAndFetchFirst(Permit.class);
	}
	
	public List<Teacher> listStudentTutors(int studentId){
		String sql = "select id, firstName, lastName, personType from Person where id in (select distinct tutorId from StudentOfTutor where studentId = :studentId)";
		return database.createQuery(sql).addParameter("studentId", studentId).executeAndFetch(Teacher.class);
	}
	
	public List<Permit> list() {
		String sql = "select id, title from Permit";
		return database.createQuery(sql).executeAndFetch(Permit.class);
	}
	
	public Object insert(String title, String description, int tutorId, int studentId){
		String sql = "insert into Permit(title, description, tutorId, studentId) values(:title, :description, :tutorId, :studentId)";
		Object result = database.createQuery(sql)
			.addParameter("title", title)
			.addParameter("description", description)
			.addParameter("tutorId", tutorId)
			.addParameter("studentId", studentId)
			.executeUpdate().getKey();
		return result;
	}
}
